/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * Main.java
 *
 * Created on 28.04.2011, 15:04:20
 */
package ch.centrisag;

import java.io.File;
import java.io.FileNotFoundException;
import javax.swing.filechooser.FileFilter;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import com.itextpdf.text.*;
import com.itextpdf.text.Font.FontFamily;
import com.itextpdf.text.pdf.*;
import java.io.FileOutputStream;

/**
 *
 * @author Patrick Wachsmuth
 * @version 1.0
 */
public class Main extends javax.swing.JFrame {

    private static Logger logger = Logger.getLogger(Main.class);
    private List<TestfallObj> testfaelle = new ArrayList<>();
    private List<KundenObj> kunden = new ArrayList<>();
    private static final Font BOLD = new Font(FontFamily.HELVETICA, 8, Font.BOLD);
    private static final Font NORMAL = new Font(FontFamily.HELVETICA, 8, Font.NORMAL);

    /** Creates new form Main */
    public Main() {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        jTextField_Input = new javax.swing.JTextField();
        jButton_Input = new javax.swing.JButton();
        jButton_Load = new javax.swing.JButton();
        jPanel2 = new javax.swing.JPanel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        jComboBox_KundenColumnStart = new javax.swing.JComboBox();
        jComboBox_KundenRowStart = new javax.swing.JComboBox();
        jComboBox_KundenColumnEnd = new javax.swing.JComboBox();
        jComboBox_KundenRowEnd = new javax.swing.JComboBox();
        jPanel3 = new javax.swing.JPanel();
        jLabel7 = new javax.swing.JLabel();
        jLabel8 = new javax.swing.JLabel();
        jLabel9 = new javax.swing.JLabel();
        jLabel10 = new javax.swing.JLabel();
        jComboBox_TestfColumnStart = new javax.swing.JComboBox();
        jComboBox_TestfRowStart = new javax.swing.JComboBox();
        jComboBox_TestfColumnEnd = new javax.swing.JComboBox();
        jComboBox_TestfRowEnd = new javax.swing.JComboBox();
        jPanel1 = new javax.swing.JPanel();
        jComboBox_Mandant = new javax.swing.JComboBox();
        jButton_Export = new javax.swing.JButton();
        jLabel2 = new javax.swing.JLabel();
        jTextField_Output = new javax.swing.JTextField();
        jButton_Output = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("EDI Testcase Generator");
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowOpened(java.awt.event.WindowEvent evt) {
                formWindowOpened(evt);
            }
        });

        jLabel1.setText("Testcases:");

        jButton_Input.setText("...");
        jButton_Input.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton_InputActionPerformed(evt);
            }
        });

        jButton_Load.setText("Load Data");
        jButton_Load.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton_LoadActionPerformed(evt);
            }
        });

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Kunden"));

        jLabel3.setText("Zeile von:");
        jLabel3.setMaximumSize(new java.awt.Dimension(55, 14));
        jLabel3.setMinimumSize(new java.awt.Dimension(55, 14));
        jLabel3.setPreferredSize(new java.awt.Dimension(55, 14));

        jLabel4.setText("Spalte von:");

        jLabel5.setText("Zelie bis:");
        jLabel5.setMaximumSize(new java.awt.Dimension(55, 14));
        jLabel5.setMinimumSize(new java.awt.Dimension(55, 14));
        jLabel5.setPreferredSize(new java.awt.Dimension(55, 14));

        jLabel6.setText("Spalte bis:");
        jLabel6.setMaximumSize(new java.awt.Dimension(55, 14));
        jLabel6.setMinimumSize(new java.awt.Dimension(55, 14));
        jLabel6.setPreferredSize(new java.awt.Dimension(55, 14));

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel4))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jComboBox_KundenRowStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_KundenColumnStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel6, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jComboBox_KundenColumnEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_KundenRowEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(509, Short.MAX_VALUE))
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_KundenRowStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_KundenRowEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel4)
                    .addComponent(jComboBox_KundenColumnStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel6, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_KundenColumnEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        jPanel3.setBorder(javax.swing.BorderFactory.createTitledBorder("Testfälle"));

        jLabel7.setText("Zeile von:");
        jLabel7.setMaximumSize(new java.awt.Dimension(55, 14));
        jLabel7.setMinimumSize(new java.awt.Dimension(55, 14));
        jLabel7.setPreferredSize(new java.awt.Dimension(55, 14));

        jLabel8.setText("Spalte von:");

        jLabel9.setText("Zeile bis:");
        jLabel9.setMaximumSize(new java.awt.Dimension(55, 14));
        jLabel9.setMinimumSize(new java.awt.Dimension(55, 14));
        jLabel9.setPreferredSize(new java.awt.Dimension(55, 14));

        jLabel10.setText("Spalte bis:");
        jLabel10.setMaximumSize(new java.awt.Dimension(55, 14));
        jLabel10.setMinimumSize(new java.awt.Dimension(55, 14));
        jLabel10.setPreferredSize(new java.awt.Dimension(55, 14));

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel7, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel8))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jComboBox_TestfRowStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_TestfColumnStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel3Layout.createSequentialGroup()
                        .addComponent(jLabel10, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jComboBox_TestfColumnEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(jPanel3Layout.createSequentialGroup()
                        .addComponent(jLabel9, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jComboBox_TestfRowEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(509, Short.MAX_VALUE))
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel7, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_TestfRowStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel9, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_TestfRowEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel8)
                    .addComponent(jLabel10, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_TestfColumnStart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBox_TestfColumnEnd, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(17, Short.MAX_VALUE))
        );

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Export"));

        jComboBox_Mandant.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Alle Kunden" }));

        jButton_Export.setText("Export");
        jButton_Export.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton_ExportActionPerformed(evt);
            }
        });

        jLabel2.setText("Export-Path:");

        jButton_Output.setText("...");
        jButton_Output.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton_OutputActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(jComboBox_Mandant, javax.swing.GroupLayout.PREFERRED_SIZE, 123, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton_Export))
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(jLabel2)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jTextField_Output, javax.swing.GroupLayout.DEFAULT_SIZE, 586, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton_Output)))
                .addContainerGap())
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextField_Output, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton_Output)
                    .addComponent(jLabel2))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jComboBox_Mandant, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton_Export))
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(jLabel1)
                        .addGap(14, 14, 14)
                        .addComponent(jTextField_Input, javax.swing.GroupLayout.DEFAULT_SIZE, 618, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton_Input))
                    .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jPanel3, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jButton_Load)
                    .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(jTextField_Input, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton_Input))
                .addGap(18, 18, 18)
                .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButton_Load)
                .addGap(18, 18, 18)
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    /**
     * Füllt die Comboboxen mit Zeilen-Nummern und Spalten-Namen
     * @param evt 
     */
    private void formWindowOpened(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowOpened
        this.setVisible(true);
        this.setLocationRelativeTo(null);

        for (char letter = 'A'; letter <= 'Z'; letter++) {
            jComboBox_KundenColumnStart.addItem(letter);
            jComboBox_KundenColumnEnd.addItem(letter);
            jComboBox_TestfColumnStart.addItem(letter);
            jComboBox_TestfColumnEnd.addItem(letter);
        }

        for (char letter = 'A'; letter <= 'Z'; letter++) {
            jComboBox_KundenColumnStart.addItem("A" + letter);
            jComboBox_KundenColumnEnd.addItem("A" + letter);
            jComboBox_TestfColumnStart.addItem("A" + letter);
            jComboBox_TestfColumnEnd.addItem("A" + letter);
        }

        for (int i = 1; i <= 100; i++) {
            jComboBox_KundenRowStart.addItem(i);
            jComboBox_KundenRowEnd.addItem(i);
            jComboBox_TestfRowStart.addItem(i);
            jComboBox_TestfRowEnd.addItem(i);
        }
    }//GEN-LAST:event_formWindowOpened

    /**
     * Öffnet einen FileChoser um das Input-File auszuwählen
     * @param evt 
     */
    private void jButton_InputActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton_InputActionPerformed
        JFileChooser fc = new JFileChooser();

        fc.setSelectedFile(new File(jTextField_Input.getText()));

        fc.setFileFilter(new FileFilter() {

            @Override
            public boolean accept(File f) {
                return f.isDirectory()
                        || f.getName().toLowerCase().endsWith(".xls") || f.getName().toLowerCase().endsWith(".XLS");
            }

            @Override
            public String getDescription() {
                return "Excel-File";
            }
        });

        int state = fc.showOpenDialog(null);

        if (state == JFileChooser.APPROVE_OPTION) {
            jTextField_Input.setText(fc.getSelectedFile().getAbsolutePath());
        }
    }//GEN-LAST:event_jButton_InputActionPerformed

    /**
     * Öffnet einen FileChoser um das Output-File auszuwählen
     * @param evt 
     */
    private void jButton_OutputActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton_OutputActionPerformed
        JFileChooser fc = new JFileChooser();

        fc.setSelectedFile(new File(jTextField_Output.getText()));
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        int state = fc.showOpenDialog(null);

        if (state == JFileChooser.APPROVE_OPTION) {
            jTextField_Output.setText(fc.getSelectedFile().getAbsolutePath());
        }
    }//GEN-LAST:event_jButton_OutputActionPerformed

    /**
     * Lädt alle Kunden und Testfälle aus dem Input-Excel
     * @param evt 
     */
    private void jButton_LoadActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton_LoadActionPerformed
        File input = new File(jTextField_Input.getText());

        if (input.exists()) {
            try {
                ColumnToValue code = new ColumnToValue();
                int start = code.getValueFromColumnName(jComboBox_KundenColumnStart.getSelectedItem().toString());
                int end = code.getValueFromColumnName(jComboBox_KundenColumnEnd.getSelectedItem().toString());
                readKunden(new CoordinateObj(Integer.parseInt(jComboBox_KundenRowStart.getSelectedItem().toString()) - 1, Integer.parseInt(jComboBox_KundenRowEnd.getSelectedItem().toString()) - 1), new CoordinateObj(start - 1, end - 1));

                start = code.getValueFromColumnName(jComboBox_TestfColumnStart.getSelectedItem().toString());
                end = code.getValueFromColumnName(jComboBox_TestfColumnEnd.getSelectedItem().toString());
                readTestfaelle(new CoordinateObj(Integer.parseInt(jComboBox_TestfRowStart.getSelectedItem().toString()) - 1, Integer.parseInt(jComboBox_TestfRowEnd.getSelectedItem().toString()) - 1), new CoordinateObj(start - 1, end - 1));

                for (int i = 0; i < kunden.size(); i++) {
                    jComboBox_Mandant.addItem(kunden.get(i).getKuerzel());
                }

                JOptionPane.showMessageDialog(null, "Testfälle geladen", "Info", JOptionPane.INFORMATION_MESSAGE);
            } catch (IOException | NumberFormatException e) {
                logger.error(e);
            }
        } else {
            JOptionPane.showMessageDialog(null, "Input-Datei existiert nicht!", "Error", JOptionPane.ERROR_MESSAGE);
        }
    }//GEN-LAST:event_jButton_LoadActionPerformed

    /**
     * Exportiert den gewünschten Kunden
     * @param evt 
     */
    private void jButton_ExportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton_ExportActionPerformed
        File output = new File(jTextField_Output.getText());

        if (output.exists()) {
            try {
                if (jComboBox_Mandant.getSelectedItem().equals("Alle Kunden")) {
                    for (int i = 0; i < kunden.size(); i++) {
                        Data2PDF(kunden.get(i));
                    }
                } else {
                    for (int i = 0; i < kunden.size(); i++) {
                        if (kunden.get(i).getKuerzel().equals(jComboBox_Mandant.getSelectedItem().toString())) {
                            Data2PDF(kunden.get(i));
                        }
                    }
                }
            } catch (DocumentException | FileNotFoundException e) {
                logger.error(e);
            }
        } else {
            JOptionPane.showMessageDialog(null, "Output-Verzeichnis existiert nicht!", "Error", JOptionPane.ERROR_MESSAGE);
        }

    }//GEN-LAST:event_jButton_ExportActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new Main().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton_Export;
    private javax.swing.JButton jButton_Input;
    private javax.swing.JButton jButton_Load;
    private javax.swing.JButton jButton_Output;
    private javax.swing.JComboBox jComboBox_KundenColumnEnd;
    private javax.swing.JComboBox jComboBox_KundenColumnStart;
    private javax.swing.JComboBox jComboBox_KundenRowEnd;
    private javax.swing.JComboBox jComboBox_KundenRowStart;
    private javax.swing.JComboBox jComboBox_Mandant;
    private javax.swing.JComboBox jComboBox_TestfColumnEnd;
    private javax.swing.JComboBox jComboBox_TestfColumnStart;
    private javax.swing.JComboBox jComboBox_TestfRowEnd;
    private javax.swing.JComboBox jComboBox_TestfRowStart;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel10;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JTextField jTextField_Input;
    private javax.swing.JTextField jTextField_Output;
    // End of variables declaration//GEN-END:variables

    /**
     * Liest alle Kunden und deren Testfälle aus dem Excel
     * @param rows
     * @param columns
     * @throws FileNotFoundException
     * @throws IOException 
     */
    private void readKunden(CoordinateObj rows, CoordinateObj columns) throws FileNotFoundException, IOException {
        kunden.clear();
        InputStream myxls = new FileInputStream(jTextField_Input.getText());
        HSSFWorkbook wb = new HSSFWorkbook(myxls);

        //With the HSSFWorkbook instance, you can now retrieve a sheet, its rows, and its cells:

        HSSFSheet sheet = wb.getSheetAt(0);       // first sheet

        for (int i = columns.getStart(); i <= columns.getEnd(); i++) {
            KundenObj kunde = new KundenObj();
            ArrayList<Integer> testf = new ArrayList<>();
            boolean first = true;

            for (int j = rows.getStart(); j <= rows.getEnd(); j++) {
                HSSFRow row = sheet.getRow(j);
                HSSFCell cell = row.getCell(i);

                if (cell != null) {
                    int cellType = cell.getCellType();

                    switch (cellType) {
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            Integer t = new Double(cell.toString()).intValue();
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            if (first) {
                                String[] tmp = cell.toString().split(" ");

                                kunde.setKuerzel(tmp[0]);
                                kunde.setSprache(tmp[1]);
                                first = false;
                            }

                            if (cell.toString().equals("X")) {
                                testf.add(j);
                            }
                            break;
                    }
                }
            }
            kunde.setTestfaelle(testf);
            kunden.add(kunde);
        }
    }

    /**
     * Liest alle Testfälle aus dem Excel
     * @param rows
     * @param columns
     * @throws FileNotFoundException
     * @throws IOException 
     */
    private void readTestfaelle(CoordinateObj rows, CoordinateObj columns) throws FileNotFoundException, IOException {
        testfaelle.clear();
        InputStream myxls = new FileInputStream(jTextField_Input.getText());
        HSSFWorkbook wb = new HSSFWorkbook(myxls);

        //With the HSSFWorkbook instance, you can now retrieve a sheet, its rows, and its cells:

        HSSFSheet sheet = wb.getSheetAt(0);       // first sheet

        for (int i = rows.getStart(); i <= rows.getEnd(); i++) {
            TestfallObj testfall = new TestfallObj();

            for (int j = columns.getStart(); j <= columns.getEnd(); j++) {
                HSSFRow row = sheet.getRow(i);
                HSSFCell cell = row.getCell(j);

                if (cell != null) {
                    if (!cell.toString().isEmpty()) {
                        int cellType = cell.getCellType();

                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                testfall.setId(new Double(cell.toString()).intValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                switch (j) {
                                    case 1:
                                        testfall.setTestfall_D(cell.toString());
                                        break;
                                    case 2:
                                        testfall.setTestfall_F(cell.toString());
                                        break;
                                    case 3:
                                        testfall.setAktion_D(cell.toString());
                                        break;
                                    case 4:
                                        testfall.setAktion_F(cell.toString());
                                        break;
                                    case 5:
                                        testfall.setErgebnis_D(cell.toString());
                                        break;
                                    case 6:
                                        testfall.setErgebnis_F(cell.toString());
                                        break;
                                    case 7:
                                        testfall.setBeachten_D(cell.toString());
                                        break;
                                    case 8:
                                        testfall.setBeachten_F(cell.toString());
                                        break;
                                }
                                break;
                        }
                    }
                }
            }
            testfaelle.add(testfall);
        }
    }

    /**
     * Speichert das übergebene Kunden-Objekt als PDF
     * @param kunde
     * @throws DocumentException
     * @throws FileNotFoundException 
     */
    private void Data2PDF(KundenObj kunde) throws DocumentException, FileNotFoundException {
        Document document = new Document(PageSize.A4.rotate());
        PdfWriter.getInstance(document, new FileOutputStream(jTextField_Output.getText() + "\\" + kunde.getKuerzel() + "_Testfälle.pdf"));
        // step 3
        document.open();
        // step 4
        PdfPTable table = new PdfPTable(6);
        table.setHorizontalAlignment(Element.ALIGN_LEFT);
        table.setWidthPercentage(100);
        table.setWidths(new float[]{new Float(0.25), new Float(2.0), new Float(2.0), new Float(2.0), new Float(2.0), new Float(0.5)});
        // the cell object
        PdfPCell cell = null;

        for (int i = 0; i < 6; i++) {
            switch (i) {
                case 0:
                    cell = new PdfPCell(new Phrase("ID", BOLD));
                    break;
                case 1:
                    if (kunde.getSprache().equals("D")) {
                        cell = new PdfPCell(new Phrase("Testfall", BOLD));
                    } else {
                        cell = new PdfPCell(new Phrase("Cas de test", BOLD));
                    }
                    break;
                case 2:
                    if (kunde.getSprache().equals("D")) {
                        cell = new PdfPCell(new Phrase("Aktion", BOLD));
                    } else {
                        cell = new PdfPCell(new Phrase("Action", BOLD));
                    }
                    break;
                case 3:
                    if (kunde.getSprache().equals("D")) {
                        cell = new PdfPCell(new Phrase("Ergebnis", BOLD));
                    } else {
                        cell = new PdfPCell(new Phrase("Résultat", BOLD));
                    }
                    break;
                case 4:
                    if (kunde.getSprache().equals("D")) {
                        cell = new PdfPCell(new Phrase("Zu Beachten", BOLD));
                    } else {
                        cell = new PdfPCell(new Phrase("Prendre en considération", BOLD));
                    }
                    break;
                case 5:
                    cell = new PdfPCell(new Phrase("OK", BOLD));
                    break;
            }

            cell.setBackgroundColor(BaseColor.ORANGE);
            cell.setPaddingTop(4);
            cell.setPaddingBottom(4);
            table.addCell(cell);
        }

        for (int i = 0; i < kunde.getTestfaelle().size(); i++) {
            TestfallObj testfall = testfaelle.get(kunde.getTestfaelle().get(i) - 1);

            for (int j = 0; j < 6; j++) {
                switch (j) {
                    case 0:
                        cell = new PdfPCell(new Phrase(String.valueOf(testfall.getId()), NORMAL));
                        break;
                    case 1:
                        if (testfall.getTestfall_D() == null) {
                            cell = new PdfPCell(new Phrase("", NORMAL));
                        } else {
                            if (kunde.getSprache().equals("D")) {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getTestfall_D()), NORMAL));
                            } else {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getTestfall_F()), NORMAL));
                            }
                        }
                        break;
                    case 2:
                        if (testfall.getAktion_D() == null) {
                            cell = new PdfPCell(new Phrase("", NORMAL));
                        } else {
                            if (kunde.getSprache().equals("D")) {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getAktion_D()), NORMAL));
                            } else {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getAktion_F()), NORMAL));
                            }
                        }
                        break;
                    case 3:
                        if (testfall.getErgebnis_D() == null) {
                            cell = new PdfPCell(new Phrase("", NORMAL));
                        } else {
                            if (kunde.getSprache().equals("D")) {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getErgebnis_D()), NORMAL));
                            } else {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getErgebnis_F()), NORMAL));
                            }
                        }
                        break;
                    case 4:
                        if (testfall.getBeachten_D() == null) {
                            cell = new PdfPCell(new Phrase("", NORMAL));
                        } else {
                            if (kunde.getSprache().equals("D")) {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getBeachten_D()), NORMAL));
                            } else {
                                cell = new PdfPCell(new Phrase(String.valueOf(testfall.getBeachten_F()), NORMAL));
                            }
                        }
                        break;
                    case 5:
                        cell = new PdfPCell(new Phrase(String.valueOf(""), NORMAL));
                        break;
                }

                cell.setPaddingTop(4);
                cell.setPaddingBottom(4);
                table.addCell(cell);
            }
        }

        document.add(table);
        // step 5
        document.close();
    }
}
